Imports System
Imports System.Collections
Imports System.Text
Imports System.Data
Imports System.Data.OleDb
Imports System.IO
Imports System.Configuration

Namespace MSAccessDB

#Region "WhereCondition"
    Public Class WhereCondition

#Region "Private Variables"
        Private _leftfield As Field = Nothing
        Private _rightfield As Field = Nothing
        Private _operator As String = Operators.EQUAL
#End Region

#Region "Constructor"
        Public Sub New()
        End Sub
        Public Sub New(ByVal pLeftField As Field, ByVal pRightField As Field, ByVal pOperator As String)
            LeftField = pLeftField
            RightField = pRightField
            [Operator] = pOperator
        End Sub
        Public Sub New(ByVal pLeftField As Field, ByVal pRightField As Field)
            LeftField = pLeftField
            RightField = pRightField
        End Sub
#End Region

#Region "Properties"
        Public Property LeftField() As Field
            Get
                Return _leftfield
            End Get
            Set(ByVal Value As Field)
                _leftfield = Value
            End Set
        End Property

        Public Property RightField() As Field
            Get
                Return _rightfield
            End Get
            Set(ByVal Value As Field)
                _rightfield = Value
            End Set
        End Property

        Public Property [Operator]() As String
            Get
                Return _operator
            End Get
            Set(ByVal Value As String)
                _operator = Value
            End Set
        End Property
#End Region

    End Class

    Public Class WhereClause
        Inherits CollectionBase

#Region "Properties"
        Default Public Property Item(ByVal pIndex As Integer) As WhereCondition
            Get
                Return Me.List(pIndex)
            End Get
            Set(ByVal Value As WhereCondition)
                Me.List(pIndex) = Value
            End Set
        End Property
#End Region

#Region "Methods"
        Public Sub Add(ByVal pWhereCondition As WhereCondition)
            Me.List.Add(pWhereCondition)
        End Sub
        Public Sub Add(ByVal pOperator As String)
            Add(New WhereCondition(New Field(""), New Field(""), pOperator))
        End Sub
#End Region

    End Class
#End Region

#Region "Statement"
    Public Class Statement

#Region "Private Variables"
        Private _databaseobject As Database = Nothing
        Private _tablenames As New ArrayList
        Private _fields As FieldCollection = New FieldCollection
        Private _orderbyfields As FieldCollection = New FieldCollection
        Private _groupbyfields As FieldCollection = New FieldCollection
        Private _whereclause As WhereClause = New WhereClause
#End Region

#Region "Properties"
        Public Property TableNames() As ArrayList
            Get
                Return _tablenames
            End Get
            Set(ByVal Value As ArrayList)
                _tablenames = Value
            End Set
        End Property
        Public Property Fields() As FieldCollection
            Get
                Return _fields
            End Get
            Set(ByVal Value As FieldCollection)
                _fields = Value
            End Set
        End Property
        Public Property OrderByFields() As FieldCollection
            Get
                Return _orderbyfields
            End Get
            Set(ByVal Value As FieldCollection)
                _orderbyfields = Value
            End Set
        End Property
        Public Property GroupByFields() As FieldCollection
            Get
                Return _groupbyfields
            End Get
            Set(ByVal Value As FieldCollection)
                _groupbyfields = Value
            End Set
        End Property
        Public Property DatabaseObject() As Database
            Get
                Return _databaseobject
            End Get
            Set(ByVal Value As Database)
                _databaseobject = Value
            End Set
        End Property
        Public Property Where() As WhereClause
            Get
                Return _whereclause
            End Get
            Set(ByVal Value As WhereClause)
                _whereclause = Value
            End Set
        End Property

#End Region

#Region "Methods"
        Public Overridable Function GetStatement() As String
            Return Nothing
        End Function
        'Following procedure creates name for parameter using supplied FieldName. For example Employee.Name will be Employee_Name
        Public Function GetParameterName(ByVal FieldName As String) As String
            Return "@" + Replace(FieldName, ".", "_")
        End Function
        Public Function ExecuteAndReturnDataset() As DataSet
            Dim retDataset As New DataSet
            Try
                Dim strTablename As String = ""
                If TableNames.Count > 0 Then strTablename = CType(TableNames(0), String)
                retDataset = DatabaseObject.ExecuteAndReturnDataSet(GetStatement(), GetParameterCollection(Fields, Where))
            Catch eSelectStatementMSAccess As Exception
                DataAccessErrorLog.LogError(eSelectStatementMSAccess.ToString())
                Throw
            End Try
            Return retDataset
        End Function
        Public Function ExecuteAndReturnDataReader(ByRef pConnection As OleDbConnection) As IDataReader
            Dim retDatareader As IDataReader = Nothing
            Try
                retDatareader = DatabaseObject.ExecuteAndReturnDataReader(GetStatement(), GetParameterCollection(Fields, Where), pConnection)
            Catch eSelectStatementMSAccess As Exception
                DataAccessErrorLog.LogError(eSelectStatementMSAccess.ToString())
                Throw
            End Try
            Return retDatareader
        End Function
        Public Function ExecuteAndReturnDataTable() As DataTable
            Dim retDataTable As New DataTable
            Try
                retDataTable = DatabaseObject.ExecuteAndReturnDataTable(GetStatement(), GetParameterCollection(Fields, Where))
            Catch eSelectStatementMSAccess As Exception
                DataAccessErrorLog.LogError(eSelectStatementMSAccess.ToString())
                Throw
            End Try
            Return retDataTable
        End Function

        Public Function ExecuteWithReturnRecordsAffected() As Integer
            Dim nRecords As Integer = 0
            Try
                nRecords = DatabaseObject.ExecuteNonQuery(GetStatement(), GetParameterCollection(Fields, Where))
            Catch e As Exception
                DataAccessErrorLog.LogError(e.ToString())
                Throw
            End Try
            Return nRecords
        End Function

        Public Function GetFieldNamesAsStatement(ByVal pFields As FieldCollection) As String
            Dim statement As New StringBuilder("")
            If (pFields.Count > 0) Then
                For i As Integer = 0 To pFields.Count - 1
                    If pFields(i).TableName <> "" Then
                        statement.Append(pFields(i).TableName)
                        statement.Append(".")
                    End If
                    If (pFields(i).SelectFieldName) Then
                        statement.Append(pFields(i).FieldName)
                    Else
                        statement.Append(pFields(i).FieldValue)
                    End If
                    If pFields(i).FieldNameAs <> "" Then
                        statement.Append(" AS [")
                        statement.Append(pFields(i).FieldNameAs)
                        statement.Append("]")
                    End If
                    statement.Append(",")
                Next
                statement.Remove(statement.Length - 1, 1)
            Else
                statement.Append("*")
            End If
            Return statement.ToString()
        End Function

        Public Function GetOrderByStatement(ByVal pFields As FieldCollection) As String
            Dim statement As New StringBuilder("")
            If (pFields.Count > 0) Then
                For i As Integer = 0 To pFields.Count - 1
                    If pFields(i).TableName <> "" Then
                        statement.Append(pFields(i).TableName)
                        statement.Append(".")
                    End If
                    statement.Append(pFields(i).FieldName)
                    statement.Append(",")
                Next
                statement.Remove(statement.Length - 1, 1)
            End If
            Return statement.ToString()
        End Function

        Public Function GetParameterNamesAsStatement(ByVal pFields As FieldCollection) As String
            Dim statement As New StringBuilder("")
            Dim i As Integer
            For i = 0 To Fields.Count - 1
                statement.Append(GetParameterName(pFields(i).FieldName))
                statement.Append(",")
            Next
            statement.Remove(statement.Length - 1, 1)
            Return statement.ToString()
        End Function
        'Added code -------
        Public Function GetParameterCollection(ByVal pFields As FieldCollection, ByVal pWhereStatement As WhereClause) As ParameterCollection
            Dim parameters As New ParameterCollection()
            Dim i As Integer
            For i = 0 To pFields.Count - 1
                If Not IsNothing(pFields(i).FieldValue) Then
                    Dim param As New OleDbParameter(GetParameterName(pFields(i).FieldName), pFields(i).FieldValue)
                    parameters.Add(param)
                End If
            Next
            If Not IsNothing(pWhereStatement) Then
                For i = 0 To pWhereStatement.Count - 1
                    If Not IsNothing(pWhereStatement(i).LeftField.FieldValue) Then
                        Dim param As New OleDbParameter(GetParameterName(pWhereStatement(i).LeftField.FieldName), pWhereStatement(i).LeftField.FieldValue)
                        parameters.Add(param)
                    End If
                    If Not IsNothing(pWhereStatement(i).RightField.FieldValue) Then
                        Dim param As New OleDbParameter(GetParameterName(pWhereStatement(i).RightField.FieldName), pWhereStatement(i).RightField.FieldValue)
                        parameters.Add(param)
                    End If
                Next
            End If
            Return parameters
        End Function

        Public Function GetWhereStatement() As String
            Dim Statement As New StringBuilder("")
            If (Where.Count > 0) Then
                Statement.Append(" WHERE ")
                Dim i As Integer
                For i = 0 To Where.Count - 1
                    If Where(i).LeftField.FieldName <> "" Then
                        Statement.Append(Operators.OPENING_BRACKET)
                    End If
                    If (Where(i).LeftField.SelectFieldName) Then
                        If Where(i).LeftField.TableName <> "" Then
                            Statement.Append(Where(i).LeftField.TableName)
                            Statement.Append(".")
                        End If
                        Statement.Append(Where(i).LeftField.FieldName)
                    Else
                        Statement.Append(GetParameterName(Where(i).LeftField.FieldName))
                    End If

                    Statement.Append(Where(i).[Operator])

                    If (Where(i).RightField.SelectFieldName) Then
                        If Where(i).RightField.TableName <> "" Then
                            Statement.Append(Where(i).RightField.TableName)
                            Statement.Append(".")
                        End If
                        Statement.Append(Where(i).RightField.FieldName)
                    Else
                        Statement.Append(GetParameterName(Where(i).RightField.FieldName))
                    End If
                    If Where(i).LeftField.FieldName <> "" Then
                        Statement.Append(Operators.CLOSING_BRACKET)
                    End If
                Next
            End If
            Return Statement.ToString()
        End Function
#End Region

    End Class
#End Region

#Region "SelectStatement"
    Public Class SelectStatement
        Inherits Statement

        Public Sub New()
        End Sub

#Region "GetStatement"
        Public Overrides Function GetStatement() As String
            Dim query As New StringBuilder("SELECT ")
            Dim i As Integer
            Try

                query.Append(GetFieldNamesAsStatement(Fields))
                query.Append(" FROM ")
                For i = 0 To TableNames.Count - 1
                    query.Append(TableNames(i))
                    query.Append(",")
                Next
                query.Remove(query.Length - 1, 1)
                query.Append(GetWhereStatement())
                If GroupByFields.Count > 0 Then
                    query.Append(" GROUP BY ")
                    query.Append(GetFieldNamesAsStatement(GroupByFields))
                End If
                If OrderByFields.Count > 0 Then
                    query.Append(" ORDER BY ")
                    query.Append(GetOrderByStatement(OrderByFields))
                End If
            Catch e As Exception
                DataAccessErrorLog.LogError(e.ToString())
                Throw
            End Try
            Return query.ToString()
        End Function 'GetStatement
#End Region

    End Class 'SelectStatement
#End Region

#Region "Field"
    Public Class Field

#Region "Private Variables"
        Private _selectfieldname As Boolean = True
        Private _fieldname As String
        'table name associated with this field
        Private _tablename As String
        'new name of field we want to use in query
        Private _fieldnameas As String
        Private _fieldvalue As Object
#End Region

#Region "Constructors"
        Public Sub New()
        End Sub

        Public Sub New(ByVal pFieldName As String)
            FieldName = pFieldName
        End Sub

        Public Sub New(ByVal pFieldName As String, ByVal pFieldValue As Object)
            FieldName = pFieldName
            FieldValue = pFieldValue
        End Sub

        'Following constructors includes pIsTableNameSupplied to ensure tablename is not considered as field value
        Public Sub New(ByVal pFieldName As String, ByVal pTableName As String, ByVal pIsTableNameSupplied As Boolean)
            FieldName = pFieldName
            If pIsTableNameSupplied Then TableName = pTableName
        End Sub

        Public Sub New(ByVal pFieldName As String, ByVal pTableName As String, ByVal pFieldNameNew As String)
            FieldName = pFieldName
            TableName = pTableName
            FieldNameAs = pFieldNameNew
        End Sub

        'Public Sub New(ByVal pFieldName As String, ByVal pFieldType As Constants.FIELDTYPES)
        '    FieldName = pFieldName
        '    FieldType = pFieldType
        'End Sub

        'Public Sub New(ByVal pFieldName As String, ByVal pTableName As String, ByVal pFieldValue As Object)
        '    FieldName = pFieldName
        '    TableName = pTableName
        '    FieldValue = pFieldValue
        'End Sub

        'Public Sub New(ByVal pFieldName As String, ByVal pTableName As String, ByVal pFieldNameNew As String, ByVal pFieldType As Constants.FIELDTYPES)
        '    FieldName = pFieldName
        '    FieldType = pFieldType
        '    TableName = pTableName
        '    FieldNameAs = pFieldNameNew
        'End Sub

        'Public Sub New(ByVal pFieldName As String, ByVal pFieldNameNew As String, ByVal pFieldType As Constants.FIELDTYPES)
        '    FieldName = pFieldName
        '    FieldType = pFieldType
        '    FieldNameAs = pFieldNameNew
        'End Sub
#End Region

#Region "FieldName"
        Public Property FieldName() As String
            Get
                Return _fieldname
            End Get
            Set(ByVal Value As String)
                _fieldname = Value
            End Set
        End Property
#End Region

#Region "FieldNameAs"
        Public Property FieldNameAs() As String
            Get
                Return _fieldnameas
            End Get
            Set(ByVal Value As String)
                _fieldnameas = Value
            End Set
        End Property
#End Region

#Region "TableName"
        Public Property TableName() As String
            Get
                Return _tablename
            End Get
            Set(ByVal Value As String)
                _tablename = Value
            End Set
        End Property
#End Region

#Region "FieldValue"
        Public Property FieldValue() As Object
            Get
                Return _fieldvalue
            End Get
            Set(ByVal Value As Object)
                _fieldvalue = Value
            End Set
        End Property
#End Region

#Region "SelectFieldName"
        Public Property SelectFieldName() As Boolean
            Get
                Return _selectfieldname
            End Get
            Set(ByVal Value As Boolean)
                _selectfieldname = Value
            End Set
        End Property
#End Region

#Region "Methods"
        'Public Overridable Function getParameterValue() As String
        '    If IsNothing(FieldValue) Then FieldValue = ""
        '    Dim retValue As New StringBuilder(FieldValue.ToString())
        '    If FieldType = Constants.FIELDTYPES.STRING Then
        '        retValue = New StringBuilder("'").Append(retValue)
        '        retValue.Append("'")
        '    End If
        '    If FieldType = Constants.FIELDTYPES.DATE Then
        '        'If retValue.ToString = "1/1/1900 12:00:00 AM" Then
        '        'retValue = New StringBuilder("null")
        '        'Else
        '        If IsDate(retValue.ToString) Then
        '            If Year(CDate(retValue.ToString)) = 1 Then
        '                retValue = New StringBuilder("1/1/1900 12:00:00 AM")
        '            End If
        '        End If
        '        If (Not IsDate(retValue.ToString)) Then retValue = New StringBuilder("1/1/1900 12:00:00 AM")
        '        retValue = New StringBuilder("'").Append(retValue).Append("'")
        '        'retValue.Append("'")
        '        'End If
        '    End If
        '    If (FieldType = Constants.FIELDTYPES.BOOLEAN) Then
        '        Select Case (FieldValue.ToString().ToLower())

        '            Case "0"
        '                retValue = New StringBuilder("0")
        '            Case "1"
        '                retValue = New StringBuilder("1")
        '            Case "false"
        '                retValue = New StringBuilder("0")
        '            Case "true"
        '                retValue = New StringBuilder("1")
        '            Case Else
        '                retValue = New StringBuilder("0")
        '        End Select
        '    End If
        '    Return retValue.ToString()
        'End Function
#End Region

    End Class
#End Region

#Region "FieldCollection"
    Public Class FieldCollection
        Inherits CollectionBase

#Region "Properties"
        Default Public Property Item(ByVal pIndex As Integer) As Field
            Get
                Return Me.List(pIndex)
            End Get
            Set(ByVal Value As Field)
                Me.List(pIndex) = Value
            End Set
        End Property
#End Region

#Region "Methods"
        Public Sub Add(ByVal pField As Field)
            Me.List.Add(pField)
        End Sub
        Public Sub Add(ByVal pFieldName As String)
            Me.List.Add(New Field(pFieldName))
        End Sub
        Public Sub Add(ByVal pFieldName As String, ByVal pFieldValue As Object)
            Me.List.Add(New Field(pFieldName, pFieldValue))
        End Sub
#End Region

    End Class
#End Region

#Region "FieldValue"
    Public Class FieldValue
        Inherits Field
#Region "Constructors"
        Public Sub New(ByVal pFieldValue As Object, ByVal pParameterName As String)
            SelectFieldName = False
            FieldValue = pFieldValue
            FieldName = pParameterName
        End Sub
#End Region
    End Class
#End Region

#Region "InsertStatement"
    Public Class InsertStatement
        Inherits Statement

#Region "GetStatement()"
        Public Overrides Function GetStatement() As String
            Dim query As New StringBuilder("insert into ")
            Try
                If Fields.Count <= 0 Then
                    Throw New Exception("There are no fields in Insert Statement. Please check Insert.Fields.Add() code")
                End If

                Dim i As Integer
                For i = 0 To TableNames.Count - 1
                    query.Append(TableNames(i))
                Next
                query.Append(" (")
                query.Append(GetFieldNamesAsStatement(Fields))
                query.Append(") values(")
                query.Append(GetParameterNamesAsStatement(Fields))
                query.Append(")")
                query.Append(GetWhereStatement())
            Catch e As Exception
                DataAccessErrorLog.LogError(e.ToString())
                Throw
            End Try
            Return query.ToString()
        End Function 'GetStatement
#End Region

    End Class 'InsertStatement
#End Region

#Region "DeleteStatement"
    Public Class DeleteStatement
        Inherits Statement

        Public Sub New()
        End Sub

#Region "Methods"
        Public Overrides Function GetStatement() As String
            Dim query As New StringBuilder("delete from ")
            Try
                Dim i As Integer
                For i = 0 To TableNames.Count - 1
                    query.Append(TableNames(i))
                    query.Append(",")
                Next
                query.Remove(query.Length - 1, 1)
                query.Append(GetWhereStatement())
            Catch e As Exception
                DataAccessErrorLog.LogError(e.ToString())
                Throw
            End Try
            Return query.ToString()
        End Function
#End Region

    End Class
#End Region

#Region "Database"
    Public Class Database

#Region "PrivateVariables"
        Private _connectionstring As String
        Private _databasename As String
        Private _databasetype As String
#End Region

#Region "Constructors"
        Public Sub New()
        End Sub
        Public Sub New(ByVal pConnectionString As String)
            ConnectionString = pConnectionString
        End Sub
#End Region

#Region "ConnectionString"
        Public Property ConnectionString() As String
            Get
                Return _connectionstring
            End Get
            Set(ByVal Value As String)
                _connectionstring = Value
            End Set
        End Property
#End Region

#Region "DatabaseName"
        Public Property DatabaseName() As String
            Get
                Return _databasename
            End Get
            Set(ByVal Value As String)
                _databasename = Value
            End Set
        End Property
#End Region

#Region "DatabaseType"
        Public Property DatabaseType() As String
            Get
                Return _databasetype
            End Get
            Set(ByVal Value As String)
                _databasetype = Value
            End Set
        End Property
#End Region

#Region "Virtual Metods"
        'Public Sub Connect(ByVal pConnectionString As String)
        '    ConnectionString = pConnectionString
        'End Sub
        Public Function CheckConnection() As Boolean
            Dim connection As New OleDbConnection(ConnectionString)
            Try
                connection.Open()
            Catch ex As OleDbException
                DataAccessErrorLog.LogError(ex.ToString)
                Return False
            Finally
                connection.Close()
            End Try
            Return True
        End Function
        Public Function ExecuteNonQuery(ByVal pQuery As String, ByRef pParams As ParameterCollection) As Integer
            Dim connection As New OleDbConnection(ConnectionString)
            Dim retValue As Integer = 0
            Try
                connection.Open()
                retValue = ExecuteNonQuery(pQuery, pParams, connection)
            Catch eDatabaseMSAccess As Exception
                DataAccessErrorLog.LogError(eDatabaseMSAccess.ToString())
                DataAccessErrorLog.LogError("Query: " + pQuery)
                Throw
            Finally
                connection.Close()
            End Try
            Return retValue
        End Function
        Public Function ExecuteNonQuery(ByVal pQuery As String, ByRef pParams As ParameterCollection, ByRef pConnection As OleDbConnection) As Integer
            Dim retValue As Integer = 0
            Try
                Dim executeCommand As OleDbCommand = New OleDbCommand(pQuery, pConnection)
                AddParameters(pParams, executeCommand.Parameters)
                retValue = executeCommand.ExecuteNonQuery()
            Catch eDatabaseMSAccess As OleDbException
                DataAccessErrorLog.LogError(eDatabaseMSAccess.ToString())
                DataAccessErrorLog.LogError("Query: " + pQuery)
                Throw
            End Try
            Return retValue
        End Function
        Public Function ExecuteScalar(ByVal pQuery As String, ByRef pParams As ParameterCollection) As Object
            Dim connection As New OleDbConnection(ConnectionString)
            Dim retValue As Integer = 0
            Try
                connection.Open()
                retValue = ExecuteScalar(pQuery, pParams, connection)
            Catch eDatabaseMSAccess As Exception
                DataAccessErrorLog.LogError(eDatabaseMSAccess.ToString())
                DataAccessErrorLog.LogError("Query: " + pQuery)
                Throw
            Finally
                connection.Close()
            End Try
            Return retValue
        End Function
        Public Function ExecuteScalar(ByVal pQuery As String, ByRef pParams As ParameterCollection, ByRef pConnection As OleDbConnection) As Object
            Dim retValue As Integer = 0
            Try
                Dim executeCommand As OleDbCommand = New OleDbCommand(pQuery, pConnection)
                AddParameters(pParams, executeCommand.Parameters)
                retValue = executeCommand.ExecuteScalar
            Catch eDatabaseMSAccess As Exception
                DataAccessErrorLog.LogError(eDatabaseMSAccess.ToString())
                DataAccessErrorLog.LogError("Query: " + pQuery)
                Throw
            End Try
            Return retValue
        End Function
        Public Function ExecuteAndReturnDataSet(ByVal pQuery As String, ByRef pParams As ParameterCollection, ByVal pTableName As String) As DataSet
            Dim selectDataset As DataSet = Nothing
            Dim connection As New OleDbConnection(ConnectionString)
            Try
                Dim selectAdapter As OleDbDataAdapter = New OleDbDataAdapter(pQuery, connection)
                selectDataset = New DataSet
                selectAdapter.Fill(selectDataset)
                If pTableName <> "" Then selectDataset.Tables(0).TableName = pTableName
            Catch eDatabaseMSAccess As Exception
                DataAccessErrorLog.LogError(eDatabaseMSAccess.ToString())
                DataAccessErrorLog.LogError("Query: " + pQuery)
                Throw
            Finally
                connection.Close()
            End Try
            Return selectDataset
        End Function
        Public Function ExecuteAndReturnDataSet(ByVal pQuery As String, ByRef pParams As ParameterCollection) As DataSet
            Return ExecuteAndReturnDataSet(pQuery, pParams)
        End Function

        Public Function ExecuteAndReturnDataReader(ByVal pQuery As String, ByRef pParams As ParameterCollection, ByRef pConnection As OleDbConnection) As OleDbDataReader
            Dim selectReader As OleDbDataReader = Nothing
            Try
                Dim selectCommand As OleDbCommand = New OleDbCommand(pQuery, pConnection)
                AddParameters(pParams, selectCommand.Parameters)
                selectReader = selectCommand.ExecuteReader()
            Catch exSQL As OleDbException
                DataAccessErrorLog.LogError(exSQL.ToString)
                DataAccessErrorLog.LogError("Query: " + pQuery)
                Throw
            End Try
            Return selectReader
        End Function
        Public Function ExecuteAndReturnDataTable(ByVal pQuery As String, ByRef pParams As ParameterCollection) As DataTable
            Dim table As New DataTable
            Dim connection As New OleDbConnection(ConnectionString)
            Try
                connection.Open()
                Dim selectReader As OleDbDataReader
                selectReader = ExecuteAndReturnDataReader(pQuery, pParams, connection)
                Dim i As Integer
                For i = 0 To selectReader.FieldCount - 1
                    table.Columns.Add(selectReader.GetName(i), selectReader.GetFieldType(i), "")
                Next

                While selectReader.Read()
                    Dim row As DataRow
                    row = table.NewRow
                    For i = 0 To selectReader.FieldCount - 1
                        row(i) = selectReader.GetValue(i)
                    Next
                    table.Rows.Add(row)
                End While
            Catch exDataAccess As Exception
                DataAccessErrorLog.LogError(exDataAccess.ToString)
                DataAccessErrorLog.LogError("Query: " + pQuery)
                Throw
            Finally
                connection.Close()
            End Try
            Return table
        End Function
        Public Function ExecuteAndReturnDataTable(ByVal pQuery As String, ByRef pParams As ParameterCollection, ByVal pTableName As String) As DataTable
            Dim table As DataTable
            table = ExecuteAndReturnDataTable(pQuery, pParams)
            table.TableName = pTableName
            Return table
        End Function

        Public Function AddParameters(ByVal pSourceParameters As ParameterCollection, ByRef pDestinationParameters As OleDbParameterCollection) As Boolean
            Dim i As Integer
            For i = 0 To pSourceParameters.Count - 1
                Dim param As New OleDbParameter(pSourceParameters(i).ParameterName, pSourceParameters(i).Value)
                pDestinationParameters.Add(param)
            Next
            Return True
        End Function

#End Region

    End Class
#End Region

#Region "Operators"
    Public NotInheritable Class Operators

#Region "Operator Constants"
        Public Const [AND] As String = " AND "
        Public Const [OR] As String = " OR "
        Public Const [LESS_THAN] As String = " < "
        Public Const [LESS_THAN_EQUAL] As String = " <= "
        Public Const [GREATER_THAN] As String = " > "
        Public Const [GREATER_THAN_EQUAL] As String = " >= "
        Public Const [EQUAL] As String = " = "
        Public Const [NOT_EQUAL] As String = " <> "
        Public Const [OPENING_BRACKET] As String = " ( "
        Public Const [CLOSING_BRACKET] As String = " ) "
#End Region

    End Class

    Public NotInheritable Class CommonFunctions

#Region "Functions"
        Public Shared Function GetTrueFalse(ByVal pValue As Object) As Boolean
            If pValue.ToString() = "1" Or pValue.ToString().ToUpper() = "TRUE" Then
                Return True
            Else
                Return False
            End If
        End Function
#End Region

    End Class
#End Region

#Region "DataAccessErrorLog"
    Public NotInheritable Class DataAccessErrorLog

#Region "Methods"
        Public Shared Sub LogError(ByVal pMessage As String)
            Dim streamWriter As StreamWriter
            Dim fileInfo As FileInfo

            Try
                fileInfo = New FileInfo("DataAccessErrorLog" + Format$(Date.Now, "_ddMMMyy") + ".txt")
                streamWriter = fileInfo.AppendText()
                streamWriter.WriteLine("")
                streamWriter.WriteLine(DateTime.Now.ToString())
                streamWriter.WriteLine(pMessage)
                streamWriter.Flush()
            Catch
                Throw
            Finally
                streamWriter.Close()
            End Try
        End Sub
#End Region

    End Class
#End Region

#Region "StoredProcedure"
    Public Class StoredProcedure

#Region "Private Variables"
        Inherits Statement
        Private _procedurename As String
        Private _parameters As FieldCollection
#End Region

        Public Sub New()
        End Sub

#Region "Properties"
        Public Property ProcedureName() As String
            Get
                Return _procedurename
            End Get
            Set(ByVal value As String)
                _procedurename = value
            End Set
        End Property
        Public Property Parameters() As FieldCollection
            Get
                Return _parameters
            End Get
            Set(ByVal value As FieldCollection)
                _parameters = value
            End Set
        End Property
#End Region

#Region "GetStatement"
        Public Overrides Function GetStatement() As String
            Dim query As New StringBuilder(_procedurename)
            Try
                If _parameters.Count > 0 Then
                    query.Append(" ")
                End If
                query.Append(GetParameterNamesAsStatement(Parameters))
            Catch e As Exception
                DataAccessErrorLog.LogError(e.ToString())
                Throw
            End Try
            Return query.ToString()
        End Function 'GetStatement
#End Region

    End Class

#End Region

#Region "UpdateStatement"
    Public Class UpdateStatement
        Inherits Statement

#Region "Constructor"
        Public Sub New()
        End Sub 'New
#End Region

#Region "GetStatement"
        Public Overrides Function GetStatement() As String
            Dim query As New StringBuilder("update ")
            Try
                If Fields.Count <= 0 Then
                    Throw New Exception("There are no fields in Update Statement. Please check Update.Fields.Add() code.")
                End If

                Dim i As Integer
                For i = 0 To TableNames.Count - 1
                    query.Append(TableNames(i))
                Next
                query.Append(" set ")
                For i = 0 To Fields.Count - 1
                    query.Append(Fields(i).FieldName)
                    query.Append("=")
                    query.Append(GetParameterName(Fields(i).FieldName))
                    query.Append(",")
                Next i
                query.Remove(query.Length - 1, 1)
                query.Append(GetWhereStatement())
                Return query.ToString()
            Catch e As Exception
                DataAccessErrorLog.LogError(e.ToString())
                Throw
            End Try
        End Function 'GetStatement
#End Region

    End Class 'UpdateStatement

    Public Class ParameterCollection
        Inherits CollectionBase

#Region "Properties"
        Default Public Property Item(ByVal pIndex As Integer) As OleDbParameter
            Get
                Return Me.List(pIndex)
            End Get
            Set(ByVal Value As OleDbParameter)
                Me.List(pIndex) = Value
            End Set
        End Property
#End Region

#Region "Methods"
        Public Sub Add(ByVal pParam As OleDbParameter)
            Me.List.Add(pParam)
        End Sub
        Public Sub Add(ByVal pParamName As String, ByVal pParamValue As Object)
            Me.List.Add(New OleDbParameter(pParamName, pParamValue))
        End Sub
#End Region

    End Class
#End Region

End Namespace
